﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace Controller
{
    // Konstrueret af:
    // Mads Rud Nielsen
    // 01-02-2013
    // Database helper

    class DBHelper
    {
        #region Properties
        
        public SqlDataReader    data { get; set; }
        private string          connectionString { get; set; }
        private SqlConnection   _connection { get; set; }
        private SqlCommand      _cmd { get; set; }
        
        #endregion

        // Local
        public DBHelper(string dataSource, string database)
        {
            this.connectionString = @"Data Source=" + dataSource + "; Initial Catalog=" + database + "; Integrated Security=True";
        }

        // External with usr and pwd
        public DBHelper(string dataSource, string user, string password)
        {
            this.connectionString = @"Data Source=" + dataSource + ";Persist Security Info=True;User ID=" + user + ";Password=" + password;
        }

        // Connect to database
        public void Connect()
        {
            _connection = new SqlConnection(this.connectionString);
            _cmd = new SqlCommand();
            _cmd.Connection = _connection;
        }

        // Åben forbindelse
        public void Open()
        {
            _connection.Open();
        }

        // Luk forbindelse
        public void Close()
        {
            _connection.Close();
        }

        // Indsæt ny row
        public bool InsertRow(string table, string fields, string values)
        {
            string sql = "INSERT INTO " + table + " (" + fields + ") VALUES (" + values + ")";
            
            // Sæt SQL
            _cmd.CommandText = sql;

            // Udfør CMD
            int affectedRows = _cmd.ExecuteNonQuery();
            
            // Retuner true eller false
            return (affectedRows == 1) ? true : false;
        }

        // Update data
        public bool UpdateRow(string table, string fieldsAndValues, string where)
        {
            string sql = "UPDATE " + table + " SET " + fieldsAndValues + " WHERE " + where;

            // Sæt SQL
            _cmd.CommandText = sql;

            // Udfør CMD
            int affectedRows = _cmd.ExecuteNonQuery();

            // Retuner true eller false
            return (affectedRows == 1) ? true : false;
        }

        // Delete row
        public bool DeleteRow(string table, string where)
        {
            string sql = "DELETE FROM " + table + " WHERE " + where;

            // Sæt SQL
            _cmd.CommandText = sql;

            // Udfør CMD
            int affectedRows = _cmd.ExecuteNonQuery();

            // Retuner true eller false
            return (affectedRows == 1) ? true : false;
        }

        // Hent data
        public void SelectData(string fields, string table, string where, string orderBy)
        {
            string sql;
            sql = "SELECT " + fields + " ";
            sql += "FROM " + table + " ";
            if (where != "")
                sql += "WHERE " + where + " ";
            if (orderBy != "")
                sql += "ORDER BY " + orderBy;

            // Sæt SQL
            _cmd.CommandText = sql;

            //sæt cmd type
            _cmd.CommandType = CommandType.Text;

            // Udfør CMD
            this.data = _cmd.ExecuteReader();
        }

        // Hent data - Stored Procedure
        public void SelectDataSP(string StoredProcedureName)
        {
            // Sæt SQL
            _cmd.CommandText = StoredProcedureName;
            
            // Sæt CMD type
            _cmd.CommandType = CommandType.StoredProcedure;

            // Clear parametre
            _cmd.Parameters.Clear();

            // Udfør CMD
            this.data = _cmd.ExecuteReader();
        }
    }
}
